import os
import sqlite3

from pandas import DataFrame

from app_config import AppConfig
from logger_util import Logger

class DBManager:
    def __init__(self, db_path=AppConfig.Path.DB_FILE):
        self.logger = Logger().get_logger()
        self.db_path = db_path
        self.conn = None
        self.cursor = None
        self._connect()
        self._create_tables()

    def _connect(self):
        try:
            os.makedirs(os.path.dirname(self.db_path), exist_ok=True)
            self.conn = sqlite3.connect(self.db_path)
            self.cursor = self.conn.cursor()
        except Exception as e:
            self.logger.exception("数据库连接失败", exc_info=e)

    def _create_tables(self):
        try:
            # 创建 stores 表
            self.cursor.execute(f'''
                CREATE TABLE IF NOT EXISTS {AppConfig.DB.STORE_TABLE} (
                    store_name TEXT PRIMARY KEY,
                    username TEXT,
                    password TEXT,
                    flag BOOLEAN
                )
            ''')
            # 创建 products 表
            self.cursor.execute(f'''
                CREATE TABLE IF NOT EXISTS {AppConfig.DB.PRODUCT_TABLE} (
                    product_id TEXT PRIMARY KEY,
                    description TEXT,
                    store_name TEXT,
                    flag BOOLEAN,
                    FOREIGN KEY (store_name) REFERENCES {AppConfig.DB.STORE_TABLE}(store_name)
                )
            ''')
            # 创建店铺数据表
            self.cursor.execute(f'''
                CREATE TABLE IF NOT EXISTS {AppConfig.DB.STORE_DATA_TABLE} (
                    fetch_date TEXT,
                    data_type TEXT,
                    store_name TEXT,
                    store_uv INTEGER,
                    store_pv INTEGER,
                    product_uv INTEGER,
                    product_pv INTEGER,
                    buyers INTEGER,
                    orders INTEGER,
                    revenue REAL,
                    conversion_rate REAL,
                    avg_order_value REAL,
                    uv_value REAL,
                    date_range TEXT,
                    PRIMARY KEY (fetch_date, data_type, store_name),
                    FOREIGN KEY (store_name) REFERENCES {AppConfig.DB.STORE_TABLE}(store_name)
                )
            ''')

            # 创建商品数据表
            self.cursor.execute(f'''
                CREATE TABLE IF NOT EXISTS {AppConfig.DB.PRODUCT_DATA_TABLE} (
                    fetch_date TEXT,
                    data_type TEXT,
                    product_id TEXT,
                    description TEXT,
                    store_name TEXT,
                    product_uv INTEGER,
                    product_pv INTEGER,
                    revenue REAL,
                    items_sold INTEGER,
                    orders INTEGER,
                    buyers INTEGER,
                    conversion_rate REAL,
                    date_range TEXT,
                    PRIMARY KEY (fetch_date, data_type, product_id),
                    FOREIGN KEY (store_name) REFERENCES {AppConfig.DB.STORE_TABLE}(store_name)
                )
            ''')
            self.conn.commit()
            # 同步字段结构（自动新增字段）
            self._sync_all_table_fields()
        except Exception as e:
            self.logger.exception("数据表创建失败", exc_info=e)

# ------    店铺表    ------
    def insert_or_update_store(self, store_name, username, password, flag=True):
        try:
            self.cursor.execute(f'''
                INSERT OR REPLACE INTO {AppConfig.DB.STORE_TABLE} (store_name, username, password, flag)
                VALUES (?, ?, ?, ?)
            ''', (store_name, username, password, flag))
            self.conn.commit()
            self.logger.info(f"店铺数据已更新: {store_name}")
        except Exception as e:
            self.logger.exception(f"更新店铺失败: {store_name}", exc_info=e)

    def get_all_stores_dict(self, flag=None):
        try:
            sql = f"SELECT store_name, username, password, flag FROM {AppConfig.DB.STORE_TABLE}"
            params = []
            if flag is not None:
                sql += " WHERE flag = ?"
                params.append(flag)
            sql += " ORDER BY store_name ASC"

            self.cursor.execute(sql, params)
            rows = self.cursor.fetchall()
            return [dict(zip(['store_name', 'username', 'password', 'flag'], r)) for r in rows]
        except Exception as e:
            self.logger.exception("查询店铺失败", exc_info=e)
            return []

    def get_store_by_name(self, store_name):
        try:
            self.cursor.execute(f"SELECT * FROM {AppConfig.DB.STORE_TABLE} WHERE store_name = ?", (store_name,))
            row = self.cursor.fetchone()
            if row:
                return {
                    'store_name': row[0],
                    'username': row[1],
                    'password': row[2],
                    'flag': row[3]
                }
            return {}
        except Exception as e:
            self.logger.exception(f"获取店铺信息失败: {store_name}", exc_info=e)
            return {}

    def delete_store(self, store_name):
        try:
            self.cursor.execute(f"DELETE FROM {AppConfig.DB.STORE_TABLE} WHERE store_name = ?", (store_name,))
            self.conn.commit()
        except Exception as e:
            self.logger.exception("删除店铺失败", e)

    # ------    商品表    ------
    def insert_or_update_product(self, product_id, description, store_name, flag=True):
        '''
        插入或更新
        :param product_id:
        :param description:
        :param store_name:
        :param flag:
        :return:
        '''
        try:
            self.cursor.execute(f'''
                INSERT OR REPLACE INTO {AppConfig.DB.PRODUCT_TABLE} (product_id, description, store_name, flag)
                VALUES (?, ?, ?, ?)
            ''', (product_id, description, store_name, flag))
            self.conn.commit()
            self.logger.info(f"商品数据已更新: {product_id}")
        except Exception as e:
            self.logger.exception(f"更新商品失败: {product_id}", exc_info=e)

    def get_all_products_dict(self, flag=None):
        try:
            sql = f"SELECT * FROM {AppConfig.DB.PRODUCT_TABLE}"
            params = []
            if flag is not None:
                sql += " WHERE flag = ?"
                params.append(flag)
            sql += " ORDER BY store_name ASC"

            self.cursor.execute(sql, params)
            return [dict(zip([col[0] for col in self.cursor.description], row)) for row in self.cursor.fetchall()]
        except Exception as e:
            self.logger.exception("查询商品失败", exc_info=e)
            return []

    def batch_update_products(self, product_list):
        '''
        批量更新
        :param product_list:
        :return:
        '''
        try:
            self.cursor.executemany(f'''
                INSERT OR REPLACE INTO {AppConfig.DB.PRODUCT_TABLE} (product_id, description, store_name, flag)
                VALUES (?, ?, ?, ?)
            ''', product_list)
            self.conn.commit()
            self.logger.info(f"批量更新商品 {len(product_list)} 条")
        except Exception as e:
            self.logger.exception("批量更新商品失败", exc_info=e)
            raise

    def get_products_by_store(self, store_name):
        try:
            self.cursor.execute(f"SELECT * FROM {AppConfig.DB.PRODUCT_TABLE} WHERE store_name = ?", (store_name,))
            rows = self.cursor.fetchall()

            columns = ['product_id', 'description', 'store_name', 'flag']
            products = [dict(zip(columns, row)) for row in rows]

            return products
        except Exception as e:
            self.logger.exception("获取店铺下商品失败", e)
            return []

    def get_product_by_id(self, product_id):
        try:
            self.cursor.execute(f"SELECT * FROM {AppConfig.DB.PRODUCT_TABLE} WHERE product_id = ?", (product_id,))
            row = self.cursor.fetchone()
            if row:
                return {
                    'product_id': row[0],
                    'description': row[1],
                    'store_name': row[2],
                    'flag': row[3]
                }
            return {}
        except Exception as e:
            self.logger.exception(f"获取商品信息失败: {product_id}", exc_info=e)
            return {}

    def delete_product(self, product_id):
        try:
            self.cursor.execute(f"DELETE FROM {AppConfig.DB.PRODUCT_TABLE} WHERE product_id = ?", (product_id,))
            self.conn.commit()
        except Exception as e:
            self.logger.exception("删除商品失败", e)

    def close(self):
        if self.conn:
            self.conn.close()
            # self.logger.info("数据库连接已关闭")

    def update_df_to_table(self,table_name: str, df: DataFrame):
        """
        批量将 Pandas DataFrame 数据插入或更新到 SQLite 表中。
        主键冲突时自动执行更新。
        :param table_name: 表名（如 'store_data'）
        :param df: 需要插入的数据，必须与表结构字段一一对应
        """
        if df.empty:
            return
        try:
            columns = self.get_table_columns(table_name)
            placeholders = ','.join(['?'] * len(columns))
            update_clause = ', '.join([f"{col}=excluded.{col}" for col in columns])

            conflict_cols = self.get_unique_constraint_columns(table_name)
            if not conflict_cols:
                raise Exception(f"{table_name} 无唯一约束，无法使用 ON CONFLICT批量更新")

            sql = f"""
                INSERT INTO {table_name} ({','.join(columns)})
                VALUES ({placeholders})
                ON CONFLICT({','.join(conflict_cols)})
                DO UPDATE SET {update_clause}
            """
            self.cursor.executemany(sql, df.values.tolist())
            self.conn.commit()
        except Exception as e:
            self.logger.exception(f"更新{table_name}数据失败", e)

    def _sync_table_fields(self, table_name: str, expected_fields: dict):
        self.cursor.execute(f"PRAGMA table_info({table_name})")
        existing_fields = {row[1] for row in self.cursor.fetchall()}

        for field_name, field_type in expected_fields.items():
            if field_name not in existing_fields:
                self.cursor.execute(
                    f"ALTER TABLE {table_name} ADD COLUMN {field_name} {field_type}"
                )

    def _sync_all_table_fields(self):
        self._sync_table_fields(AppConfig.DB.STORE_DATA_TABLE, {
            "fetch_date": "TEXT",
            "data_type": "TEXT",
            "store_name": "TEXT",
            "store_uv": "INTEGER",
            "store_pv": "INTEGER",
            "product_uv": "INTEGER",
            "product_pv": "INTEGER",
            "buyers": "INTEGER",
            "orders": "INTEGER",
            "revenue": "REAL",
            "conversion_rate": "REAL",
            "avg_order_value": "REAL",
            "uv_value": "REAL",
            "date_range": "TEXT",
        })

        self._sync_table_fields(AppConfig.DB.PRODUCT_DATA_TABLE, {
            "fetch_date": "TEXT",
            "data_type": "TEXT",
            "product_id": "TEXT",
            "description": "TEXT",
            "store_name": "TEXT",
            "product_uv": "INTEGER",
            "product_pv": "INTEGER",
            "revenue": "REAL",
            "items_sold": "INTEGER",
            "orders": "INTEGER",
            "buyers": "INTEGER",
            "conversion_rate": "REAL",
            "date_range": "TEXT",
        })

    def get_table_columns(self, table_name: str) -> list:
        """获取数据库表字段名列表"""
        self.cursor.execute(f"PRAGMA table_info({table_name})")
        return [row[1] for row in self.cursor.fetchall()]

    def get_unique_constraint_columns(self, table_name: str):
        """
        获取指定 SQLite 表中第一个唯一索引的列名列表，用于 ON CONFLICT 子句。
        """
        # 获取所有索引
        self.cursor.execute(f"PRAGMA index_list({table_name})")
        indexes = self.cursor.fetchall()

        for index in indexes:
            index_name = index[1]
            is_unique = index[2]  # 1 表示 UNIQUE 索引

            if is_unique:
                self.cursor.execute(f"PRAGMA index_info({index_name})")
                index_info = self.cursor.fetchall()
                # 返回唯一索引对应的所有列名
                return [row[2] for row in index_info]

        return []  # 没有唯一约束时返回空列表

    #查询
    def query_table_data(self, table_name, start_date, end_date, data_type,
                         store_name=None, product_id=None, product_name=None):
        sql = f"SELECT * FROM {table_name} WHERE fetch_date BETWEEN ? AND ? AND data_type = ?"
        params = [start_date, end_date, data_type]

        self.cursor.execute(f"PRAGMA table_info({table_name})")
        columns = [col[1] for col in self.cursor.fetchall()]

        if "store_name" in columns and store_name:
            sql += " AND store_name LIKE ?"
            params.append(f"%{store_name}%")
        if "product_id" in columns and product_id:
            sql += " AND product_id LIKE ?"
            params.append(f"%{product_id}%")
        if table_name == AppConfig.DB.PRODUCT_DATA_TABLE and product_name:
            sql += " AND product_id IN (SELECT product_id FROM products WHERE description LIKE ?)"
            params.append(f"%{product_name}%")

        self.cursor.execute(sql, params)
        rows = self.cursor.fetchall()
        headers = [desc[0] for desc in self.cursor.description]

        if table_name != AppConfig.DB.PRODUCT_DATA_TABLE:
            return headers, rows

        # 找到 description 所在索引
        desc_idx = headers.index("description")

        # 替换 description 值
        enriched_rows = []
        for row in rows:
            row = list(row)
            pid = row[headers.index("product_id")]
            sname = row[headers.index("store_name")]

            self.cursor.execute("SELECT description FROM products WHERE product_id = ? AND store_name = ?",
                                (pid, sname))
            result = self.cursor.fetchone()
            row[desc_idx] = result[0] if result else ""

            enriched_rows.append(tuple(row))

        return headers, enriched_rows